package com.huarun.common.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSONArray;

/**
 * @author hqy
 * @创建时间   2018-10-29 10:41
 * @Description excel导入
 */
@Component
public class ExcelReadUtil {
	protected static Logger logger = LoggerFactory.getLogger(ExcelReadUtil.class);

	/**
	 * 读取excel文件数据
	 * @param fileName 文件名
	 * @param in 流
	 * @return 
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static JSONArray getDataFromExcel(String fileName, InputStream in)
			throws InstantiationException, IllegalAccessException {
		//判断是否为excel类型文件
		if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
			System.out.println("文件不是excel类型");
		}

		Workbook wookbook = null;
		if (fileName.endsWith(".xls")) {
			try {
				//用.xls结尾
				wookbook = new HSSFWorkbook(in);//得到工作簿
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		} else {
			try {
				//用.xlsx结尾
				wookbook = new XSSFWorkbook(in);//得到工作簿
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		//得到一个工作表
		Sheet sheet = wookbook.getSheetAt(0);

		//获得表头
		Row rowHead = sheet.getRow(0);
		//获取列数
		int rowNo = rowHead.getPhysicalNumberOfCells();
		//获得数据的总行数
		int totalRowNum = sheet.getLastRowNum();
		//获得所有数据
		JSONArray array = new JSONArray();
		Map<Object, Object> map = null;
		for (int i = 1; i <= totalRowNum; i++) {
			map = new HashMap<>();
			//获得第i行对象
			Row row = sheet.getRow(i);
			//获取第i行对应的每一列数据
			for (int j = 0; j < rowNo; j++) {
				if (row.getCell(j) != null) {
					map.put(getRightTypeCell(rowHead.getCell(j)), getRightTypeCell(row.getCell(j)));
				}
			}
			array.add(map);
		}
		return array;
	}

	/**
	 *     
	 * @param cell 一个单元格的对象
	 * @return 返回该单元格相应的类型的值
	 */
	public static Object getRightTypeCell(Cell cell) {
		Object object = null;
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING: {
				object = cell.getStringCellValue();
				break;
			}
			case Cell.CELL_TYPE_NUMERIC: {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				object = cell.getNumericCellValue();
				break;
			}

			case Cell.CELL_TYPE_FORMULA: {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				object = cell.getNumericCellValue();
				break;
			}

			case Cell.CELL_TYPE_BLANK: {
				cell.setCellType(Cell.CELL_TYPE_BLANK);
				object = cell.getStringCellValue();
				break;
			}
		}
		return object;
	}

}
